
package es.udc.emarketapp.emarket.model.category.dao;

import es.udc.emarketapp.emarket.model.category.vo.CategoryVO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;
import java.util.ArrayList;
import java.util.List;


public abstract class AbstractSQLCategoryDAO implements SQLCategoryDAO {

    public boolean exists(Connection connection, Long idCat)
    	throws InternalErrorException {

        PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	try {
		/* create "preparedStatement"*/
		String queryString =
			"SELECT id_cat FROM categoria WHERE id_cat = ?";

		preparedStatement = connection.prepareStatement(queryString);

		/* fill "preparedStatement" */
		preparedStatement.setLong(1, idCat);

		/* execute query */
		resultSet = preparedStatement.executeQuery();

		return resultSet.next();

	} catch (SQLException e) {
		throw new InternalErrorException(e);
	} finally {
		GeneralOperations.closeResultSet(resultSet);
		GeneralOperations.closeStatement(preparedStatement);
	}
    } // exists


    public CategoryVO findById(Connection connection, Long idCat)
        throws InstanceNotFoundException, InternalErrorException {

	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	try {

		/* create "preparedStatement" */
		String queryString =
			"SELECT cat_padre, nom_cat FROM categoria " +
                        "WHERE id_cat = ?";

		preparedStatement = connection.prepareStatement(queryString);

		/* fill "preparedStatement" */
		preparedStatement.setLong(1, idCat);

		/* execute query */
		resultSet = preparedStatement.executeQuery();

		if (!resultSet.next()) {
			throw new InstanceNotFoundException(
                            idCat, CategoryVO.class.getName());
		}

		/* get results */
        Long catPadre = new Long(resultSet.getLong(1));
        String nomCat = resultSet.getString(2);
		

		return new CategoryVO(idCat, catPadre, nomCat);

	} catch (SQLException e) {
		throw new InternalErrorException(e);
	} finally {
		GeneralOperations.closeResultSet(resultSet);
		GeneralOperations.closeStatement(preparedStatement);
	}
    } // findById


    public CategoryVO findByName(Connection connection, String name)
	throws InstanceNotFoundException, InternalErrorException {

	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	try {

		/* create "preparedStatement" */
		String queryString = "SELECT id_cat, cat_padre" +
                         " FROM categoria WHERE nom_cat LIKE ?";

		preparedStatement = connection.prepareStatement(queryString);

		/* fill "preparedStatement" */
		preparedStatement.setString(1, name );

		/* execute query */
		resultSet = preparedStatement.executeQuery();

		if (!resultSet.next()) {
			throw new InstanceNotFoundException(name,
					CategoryVO.class.getName());
		}

		/* get results */
		Long idCat = new Long(resultSet.getLong(1));
		Long catPadre = new Long(resultSet.getLong(2));

		return new CategoryVO(idCat, catPadre, name);

	} catch (SQLException e) {
		throw new InternalErrorException(e);
	} finally {
		GeneralOperations.closeResultSet(resultSet);
		GeneralOperations.closeStatement(preparedStatement);
	}
    } // findByName


    public List<CategoryVO> getSons(Connection connection, Long catPadre,
	int startIndex, int count) throws InternalErrorException {

	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	try {

		/* create "preparedStatement" */
		String queryString = "SELECT id_cat, nom_cat FROM categoria " +
				"WHERE cat_padre = ? ORDER BY nom_cat";

		preparedStatement = connection.prepareStatement(queryString,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

		/* fill "preparedStatement" */
		preparedStatement.setLong(1, catPadre);

		/* execute query */
		resultSet = preparedStatement.executeQuery();

		/* read objects */
		List<CategoryVO> categoriaVOs = new ArrayList<CategoryVO>();
		int currentCount = 0;

		if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

			do {
                            Long idCat = new Long(resultSet.getLong(1));
                            String nomCat = resultSet.getString(2);

                            categoriaVOs.add(new CategoryVO(
				idCat, catPadre, nomCat));

                            currentCount++;

			} while (resultSet.next() && (currentCount < count));
		}

		/* return value objects */
		return categoriaVOs;

	} catch (SQLException e) {
		throw new InternalErrorException(e);
	} finally {
		GeneralOperations.closeResultSet(resultSet);
		GeneralOperations.closeStatement(preparedStatement);
	}
    } // getSons


    public void update(Connection connection, CategoryVO categoryVO)
	throws InstanceNotFoundException, InternalErrorException {

	PreparedStatement preparedStatement = null;

	try {

            /* create "preparedStatement" */
            String queryString = "UPDATE categoria SET nom_cat = ?, " +
                    "cat_padre = ? WHERE id_cat = ?";

            preparedStatement = connection.prepareStatement(queryString);

            /* fill "preparedStatement" */
            preparedStatement.setString(1, categoryVO.getNomCat());
            preparedStatement.setLong(2, categoryVO.getCatPadre());
            preparedStatement.setLong(3, categoryVO.getIdCat());

            /* execute query */
            int updateRows = preparedStatement.executeUpdate();

            if (updateRows == 0) {
            	throw new InstanceNotFoundException(categoryVO.getIdCat(),
			CategoryVO.class.getName());
            }

            if (updateRows > 1) {
		throw new SQLException("Duplicate row for identifier = '" +
			categoryVO.getIdCat() + "' in table 'Categoria'");
            }


	} catch (SQLException e) {
		System.out.println(e.getMessage());
		throw new InternalErrorException(e);
	} finally {
		GeneralOperations.closeStatement(preparedStatement);
	}
    } // update


    public void remove(Connection connection, Long idCat)
	throws InstanceNotFoundException, InternalErrorException {

	PreparedStatement preparedStatement = null;

	try {
            /* create "preparedStatement" */
            String queryString = "DELETE FROM categoria WHERE id_cat = ?";
            preparedStatement = connection.prepareStatement(queryString);

            /* fill "preparedStatement" */
            preparedStatement.setLong(1, idCat);

            /* execute query */
            int removeRows = preparedStatement.executeUpdate();

            if (removeRows == 0) {
		throw new InstanceNotFoundException(idCat,
			CategoryVO.class.getName());
            }
	} catch (SQLException e) {
		throw new InternalErrorException(e);
	} finally {
		GeneralOperations.closeStatement(preparedStatement);
	}
    } // remove


}
